package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.ContractInfo;
import com.sl.au.entity.ExdeTbOPPlanTask;
import com.sl.au.entity.ExdeTbOPPlanTaskDetail;
import com.sl.au.entity.ExdeTbOPPlanTaskDetailReplace;

@Transactional(readOnly = true)
public interface ExdeSchedulingRepository extends JpaRepository<ExdeTbOPPlanTask, String> {

	/**
	 * 根据查询条件查询计划任务数据
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select e from ExdeTbOPPlanTask e where (e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%')"
			+ " and e.cState like '%'+?2+'%' ")
	Page<ExdeTbOPPlanTask> getContractInfos(String key, String state, Pageable pageable);
	
	/**
	 * 根据查询条件及部门查询合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param pageable
	 * @return
	 */
	@Query(" select e from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and e.cCreateDept = ?2 and "
			+ " e.cState like '%'+?3+'%' ")
	Page<ExdeTbOPPlanTask> getContractInfos(String key, String superior, String state, Pageable pageable);

	/**
	 * 根据查询条件及部门片区查询合同数据
	 * 
	 * @param key
	 * @param superior
	 * @param areaName
	 * @param pageable
	 * @return
	 */
	@Query(" select e from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and (e.cCreateDept=?2 and e.AreaName=?3 ) and "
			+ " e.cState like '%'+?4+'%' ")
	Page<ExdeTbOPPlanTask> getContractInfos(String key, String superior, String areaName, String state, Pageable pageable);

	/**
	 * 根据查询条件查询当前操作人的合同数据
	 * 
	 * @param key
	 * @param userId
	 * @param pageable
	 * @return
	 */
	@Query(" select e from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and e.user.id = ?2 and e.cState like '%'+?3+'%' ")
	Page<ExdeTbOPPlanTask> getContractInfosByUser(String key, String userId, String state, Pageable pageable);
	
	@Query(" select count(*) from ExdeTbOPPlanTask e where (e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%') and e.cState like '%'+?2+'%' ")
	long getContractInfoCount(String key, String state );
	
	@Query(" select count(*) from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and e.cCreateDept = ?2 and "
			+ "e.cState like '%'+?3+'%' ")
	long getContractInfoCount(String key, String superior, String state);
	
	@Query(" select count(*) from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and (e.cCreateDept=?2 and e.AreaName=?3 ) and "
			+ " e.cState like '%'+?4+'%' ")
	long getContractInfoCount(String key, String superior, String areaName, String state);
	
	@Query(" select count(*) from ExdeTbOPPlanTask e where ( e.cPlanNumber like '%'+?1+'%' or e.cAcceptPersonName like '%'+?1+'%' "
			+ "or e.cContractNumber like '%'+?1+'%' or e.cUserUnit like '%'+?1+'%' or e.cIsGenerated like '%'+?1+'%' or e.cType like '%'+?1+'%'"
			+ " ) and e.user.id = ?2 and e.cState like '%'+?3+'%' ")
	long getContractInfoByUserCount(String key, String userId, String state);
	
	@Query(" select count(*) from ExdeTbOPPlanTask e where e.cPlanNumber like '%'+?1+'%' ")
	long getCodeCount(String planNumber);

	@Query(" select e from ExdeTbOPPlanTaskDetail e where e.opPlanTaskId = ?1 ")
	List<ExdeTbOPPlanTaskDetail> getPlanTaskDetail(String id, Pageable pageable);
	
	@Query(" select e from ExdeTbOPPlanTaskDetailReplace e where e.OpPlanTaskId = ?1 order by e.createTime asc")
	List<ExdeTbOPPlanTaskDetailReplace> getPlanTaskDetailReplace(String id, Pageable pageable);

	@Query(" select count(*) from ExdeTbOPPlanTaskDetail e where e.opPlanTaskId = ?1 ")
	long getPlanTaskDetailCount(String id);

	@Query(" select count(*) from ExdeTbOPPlanTaskDetailReplace e where e.OpPlanTaskId = ?1 ")
	long getPlanTaskDetailReplaceCount(String id);

	@Query(" select e from ExdeTbOPPlanTask e where e.contractInfo.id = ?1 and e.contractType = ?2")
	ExdeTbOPPlanTask findBycontractId(String id, String string);
	
}
